﻿Imports System.Runtime.CompilerServices
Imports System.Xml
Imports System.IO

Module Module1

    Function Confirm(prompt As String) As Boolean
        Console.WriteLine(prompt)
        Return Console.ReadKey.Key = ConsoleKey.S
    End Function

    Sub Main()
        If Not Confirm("¿Comenzamos (XML a tablas *_FM5)? (S/N)") Then Exit Sub
        ProcessAll("D:\comunes\Proyectos\ASINDO.NET\BD Filemaker 20110926", New SqlServerTableProcessor)
        'ProcessOne("D:\comunes\Proyectos\ASINDO.NET\BD Filemaker 20110321\familias.xml", New SqlServerTableProcessor)
        'Exit Sub
        If Not Confirm("¿Comenzamos (tablas *_FM5 a esquema moderno)? (S/N)") Then Exit Sub
        Dim ctx As New Asindo3.AsindoContext
        Dim imp As New Asindo3.ImportarFilemaker With {.Contexto = ctx}
        imp.Importar()
        Console.WriteLine("OK!!")
        Console.ReadLine()
        Exit Sub
    End Sub

    Sub ProcessAll(ByVal dn As String, ByVal procesador As TableProcessor)
        Dim carpetaOrigen As New DirectoryInfo(dn)


        procesador.Initialize()

        For Each xmlOrigen In carpetaOrigen.GetFiles("*.xml")
            ProcesarXml(xmlOrigen, procesador)
        Next
        procesador.Finish()
    End Sub

    Sub ProcessOne(ByVal fn As String, ByVal procesador As TableProcessor)
        Dim xmlOrigen As New FileInfo(fn)

        procesador.Initialize()
        ProcesarXml(xmlOrigen, procesador)
        procesador.Finish()
    End Sub

    Sub ProcesarXml(ByVal xmlOrigen As FileInfo, ByVal procesador As TableProcessor)
        Dim table = LeerXml(xmlOrigen)
        procesador.Process(xmlOrigen, table)
        Console.WriteLine("OK!!")
    End Sub

    Function LeerXml(ByVal xmlOrigen As FileInfo) As TableInfo
        Console.WriteLine("Procesando " & xmlOrigen.Name)
        Dim doc As New XmlDocument
        Dim table As New TableInfo
        Dim regexp As New System.Text.RegularExpressions.Regex("\d+", Text.RegularExpressions.RegexOptions.Compiled)
        Console.WriteLine("Cargando XML...")
        doc.Load(xmlOrigen.FullName)

        Dim database = doc.SelectSingleByName("DATABASE")
        table.Name = database.Attributes("NAME").Value.Replace(".", "_")

        Dim xmlfields = doc.SelectNodesByName("FIELD")

        For Each xmlfield As XmlNode In xmlfields
            Dim field = New FieldInfo( _
                xmlfield.Attributes("NAME").Value, _
                xmlfield.Attributes("TYPE").Value, allowsNull:=True)
            If field.FMType = "TEXT" Then
                field.Type = "varchar"
            End If
            field.AllowsNull = (xmlfield.Attributes("EMPTYOK").Value = "YES")

            table.Fields.Add(field)
        Next
        Dim xmlrows = doc.SelectNodesByName("ROW")
        For i = 0 To xmlrows.Count - 1
            Dim xmlrow As XmlNode = xmlrows(i)
            table.Key.Rows.Add(xmlrow.Attributes("RECORDID").Value)
            Dim xmlcols = xmlrow.SelectNodesByName("DATA")

            For j = 0 To xmlcols.Count - 1
                Dim data = xmlcols(j).InnerText

                If String.IsNullOrEmpty(data) Then
                    If table.Fields(j).AllowsNull Then
                        data = Nothing
                    ElseIf table.Fields(j).FMType = "TEXT" Then
                        data = ""
                    Else
                        Throw New Exception("asfd")
                    End If
                Else
                    Select Case table.Fields(j).FMType
                        Case "TEXT"
                            If data.Length > table.Fields(j).Size Then
                                table.Fields(j).Size = data.Length
                                If data.Length > 1000 Then
                                    table.Fields(j).Type = "ntext"
                                Else
                                    table.Fields(j).Type = "nvarchar"
                                End If
                            End If
                        Case "NUMBER"
                            If data.Contains(".") Then
                                table.Fields(j).Type = "float"
                            End If
                            If Not IsNumeric(data) Then
                                Dim mtch = regexp.Match(data)
                                data = mtch.Value
                            End If
                        Case "DATE"
                            If data.Contains(":") Then
                                data = data.Replace(":", "/")
                            End If
                            data = CDate(data).ToString("yyyy-MM-dd")
                        Case "TIME"
                            If data.Length > 0 Then
                                Dim sa = data.Split(":"c)
                                If CInt(sa(0)) >= 24 OrElse _
                                   CInt(sa(0)) < 0 Then
                                    data = ""
                                    Exit Select
                                End If
                                If sa.Length >= 2 AndAlso ( _
                                   CInt(sa(1)) >= 60 OrElse _
                                   CInt(sa(1)) < 0) Then
                                    data = ""
                                    Exit Select
                                End If
                                If sa.Length = 3 AndAlso ( _
                                   CInt(sa(2)) >= 60 OrElse _
                                   CInt(sa(2)) < 0) Then
                                    data = ""
                                    Exit Select
                                End If
                                If sa.Length = 1 Then
                                    data = sa(0) & ":00:00"
                                ElseIf sa.Length = 2 Then
                                    data = String.Format("{0}:{1}:00", sa(0), sa(1))
                                ElseIf sa.Length = 3 Then
                                    data = String.Format("{0}:{1}:{2}", sa(0), sa(1), sa(2))
                                Else
                                    Throw New InvalidOperationException()
                                End If
                            End If
                        Case Else
                            Throw New Exception
                    End Select
                End If
                table.Fields(j).Rows.Add(data)
            Next j
            If i Mod 1000 = 0 Then
                Console.WriteLine(i & " filas")
            End If
        Next i
        Console.WriteLine(table.Fields(0).Rows.Count & " filas")

        Return table
    End Function

    <Extension()> _
    Public Function SelectSingleByName(ByVal n As XmlNode, ByVal name As String) As XmlNode
        If n.Name = name Then
            Return n
        Else
            For Each child As XmlNode In n.ChildNodes
                Dim n2 = child.SelectSingleByName(name)
                If n2 IsNot Nothing Then
                    Return n2
                End If
            Next
            Return Nothing
        End If
    End Function

    <Extension()> _
    Public Function SelectNodesByName(ByVal n As XmlNode, ByVal name As String) As List(Of XmlNode)
        Dim result As New List(Of XmlNode)
        SelectNodesByNameAux(n, result, name)
        Return result
    End Function

    Private Sub SelectNodesByNameAux(ByVal n As XmlNode, ByRef result As List(Of XmlNode), ByVal name As String)
        If n.Name = name Then
            result.Add(n)
        Else
            For Each child As XmlNode In n.ChildNodes
                SelectNodesByNameAux(child, result, name)
            Next
        End If
    End Sub

    Public Sub TransferirDatos()

    End Sub
End Module
